#!/bin/sh
echo "存取款项目"
echo "创建日期：2025-05-26"
echo "创建人员：2504二组"
echo "版本功能：清洗ods层客户基本信息表的数据落地到dw层"
echo "来源表：ods_cust_info_01 落地表：dw_cust_info"
echo "开始编写脚本，封装sql，需要先把sql跑通然后再进行封装"
hive -v -e "set hive.exec.mode.local.auto=true;
insert overwrite table dw01.dw_cust_info
SELECT 
    cust_id,
    CASE 
        WHEN chinese_part != '' THEN regexp_replace(chinese_part, '\\s+', '')  
        ELSE regexp_replace(regexp_replace(trim(cust_name), '[^a-zA-Z\\s]', ''), '\\s+', ' ') 
    END AS final_cust_name,
    amt,
    dt,
    CASE 
        WHEN phone IS NULL OR LENGTH(TRIM(phone)) = 0 THEN '99999999999'
        WHEN regexp_extract(phone, '[1][0-9]{10}', 0) = '' THEN '99999999999'
        ELSE regexp_extract(phone, '[1][0-9]{10}', 0)
    END AS phone,
    acctno,
    c.code_id,
    code_type,
    code_comment,
    b.org_id,
    nvl(org_name,'未知'),
    nvl(org_level,'未知'),
    nvl(org_addr,'未知'),
    current_timestamp() AS process_time  -- Hive中获取当前时间戳
FROM (SELECT 
       a.*,
       regexp_replace(trim(cust_name), '[^\u4e00-\u9fa5]', '') AS chinese_part
     FROM ods01.ods_cust_info_01 a) b
LEFT JOIN ods01.ods_code_info_01 c ON b.code_id = c.code_id
LEFT JOIN ods01.ods_org_info_01 d ON b.org_id = d.org_id;"
echo $?
echo "--数据已清洗完成，接下来进行数据的验证"
c=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw01.dw_cust_info;"`
echo "客户基表dw的数据量为：${c}条"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from ods01.ods_cust_info_01;"`
echo "ods层的数据量为：${c1}条"
if [ "$c1" -eq "$c" ];then
echo "清洗成功"
else
echo "清洗失败"
fi




